package net.hn.hnms.biz.plan.mapper;

import com.baomidou.mybatisplus.annotation.InterceptorIgnore;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import net.hn.hnms.biz.plan.domain.DispatchKeepWatch;
import net.hn.hnms.biz.plan.request.DispatchKeepWatchReq;
import net.hn.hnms.biz.plan.vo.DispatchKeepWatchVO;
import net.hn.hnms.sys.common.mybatis.core.mapper.BaseMapperPlus;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.springframework.stereotype.Repository;

import java.util.List;

@Mapper
@Repository
public interface DispatchKeepWatchMapper extends BaseMapperPlus<DispatchKeepWatchMapper,DispatchKeepWatch, DispatchKeepWatchVO> {

    Page<DispatchKeepWatchVO> selectPageList(Page<Object> build, @Param("query") DispatchKeepWatchReq dispatchKeepWatch);


//    @Select("<script> " +
//            "  select keep_watch_date as keepWatchDate,dw.keep_watch_id as keepWatchId,dp.keep_watch_person_name as leadPersonNames,dw.lead_person_ids, shift_id, " +
//            " case shift_id when  '0298c8a9d0639d715a0925f7b1ec2f55' then '1'  when '0e0220e4af825d70d0932cc4504cd80d' then '2' when  '70b1dacd7bf4db32cd37c1322a290005' then '3' else '' end as shiftName  " +
//            "  from dispatch_keep_watch  dw  " +
//            "  left join dispatch_keep_watch_person dp on dp.keep_watch_person_id = dw.lead_person_ids   " +
//            "   where dw.type= '2' and dp.del_flag = '0' and dw.del_flag ='0' and  to_char(\"keep_watch_date\",'yyyy-mm') =#{query} " +
//            "  order by keep_watch_date,shiftName  " +
//            "</script>")
//    @Select("<script>" +
//            " select  keep_watch_date , " +
//            "  string_agg(case shift_id when  '0298c8a9d0639d715a0925f7b1ec2f55' then shift_id else NULL end,',') as  morningNum, " +
//            "  string_agg(case shift_id when  '0298c8a9d0639d715a0925f7b1ec2f55' then dp.keep_watch_person_name else NULL end,',') as  morningName, " +
//            "  string_agg(case shift_id when  '0e0220e4af825d70d0932cc4504cd80d' then shift_id else NULL end,',') as  noonNum, " +
//            "  string_agg(case shift_id when  '0e0220e4af825d70d0932cc4504cd80d' then dp.keep_watch_person_name else NULL end,',') as  noonName, " +
//            "  string_agg(case shift_id when  '70b1dacd7bf4db32cd37c1322a290005' then shift_id else NULL end,',') as  nightNum, " +
//            "  string_agg(case shift_id when  '70b1dacd7bf4db32cd37c1322a290005' then dp.keep_watch_person_name else NULL end,',') as  nightName " +
//            " from dispatch_keep_watch  dw  " +
//            " left join dispatch_keep_watch_person dp on dp.keep_watch_person_id = dw.lead_person_ids  " +
//            " where dw.type= '2' and dp.del_flag = '0' and dw.del_flag ='0' and  to_char(\"keep_watch_date\",'yyyy-mm') =#{query} " +
//            "  group by keep_watch_date; " +
//            "  " +
//            "</script>")
    @Select("<script>" +
            " select keep_watch_date, to_char(keep_watch_date,'dd') as dutyId ," +
            " string_agg(case shift_id when  '0298c8a9d0639d715a0925f7b1ec2f55' then dw.keep_watch_id " +
            "  when '0e0220e4af825d70d0932cc4504cd80d' then dw.keep_watch_id   " +
            "  when  '70b1dacd7bf4db32cd37c1322a290005' then dw.keep_watch_id else NULL end,',') as  keep_watch_id,  " +
            " string_agg(case shift_id when  '0298c8a9d0639d715a0925f7b1ec2f55' then dp.keep_watch_person_id " +
            "  when '0e0220e4af825d70d0932cc4504cd80d' then dp.keep_watch_person_id   " +
            "  when  '70b1dacd7bf4db32cd37c1322a290005' then dp.keep_watch_person_id else NULL end,',') as  lead_person_ids,   " +
            "    case shift_id when  '0298c8a9d0639d715a0925f7b1ec2f55' then '0298c8a9d0639d715a0925f7b1ec2f55'  " +
            " when '0e0220e4af825d70d0932cc4504cd80d' then '0e0220e4af825d70d0932cc4504cd80d' " +
            " when  '70b1dacd7bf4db32cd37c1322a290005' then '70b1dacd7bf4db32cd37c1322a290005' else '' end as shift_id, " +
            " string_agg(case shift_id when  '0298c8a9d0639d715a0925f7b1ec2f55' then dp.keep_watch_person_name " +
            "  when '0e0220e4af825d70d0932cc4504cd80d' then dp.keep_watch_person_name   " +
            "  when  '70b1dacd7bf4db32cd37c1322a290005' then dp.keep_watch_person_name else NULL end,',') as  morningName,   " +
            "   case shift_id when  '0298c8a9d0639d715a0925f7b1ec2f55' then '1'  " +
            " when '0e0220e4af825d70d0932cc4504cd80d' then '2' " +
            " when  '70b1dacd7bf4db32cd37c1322a290005' then '3' else '' end as shiftName " +
            "  from dispatch_keep_watch  dw " +
            "  left join dispatch_keep_watch_person dp on dp.keep_watch_person_id = dw.lead_person_ids  " +
            "  where dw.type= '2' and dp.del_flag = '0' and dw.del_flag ='0' " +
            " <if test='flag == 1 '> "+
            " and  to_char(\"keep_watch_date\",'yyyy-mm') =#{query} " +
            " </if>"+
            " <if test='flag == 2 '> "+
            " and  to_char(\"keep_watch_date\",'yyyy') =#{query} " +
            " </if>"+
            " <if test='mineCode != null and mineCode != \"\" '> "+
            " and dw.mine_code = #{mineCode} " +
            " </if>"+
            "  GROUP BY keep_watch_date,shiftName,shift_id " +
            "  order by keep_watch_date,shiftName,shift_id " +
            "  " +
            "</script>")
    List<DispatchKeepWatchVO> leadShiftList(@Param("query") String beginDate,@Param("flag") String flag,@Param("mineCode") String mineCode);


    @Select("<script>" +
            " select  keep_watch_date , " +
            "  string_agg(case shift_id when  '0298c8a9d0639d715a0925f7b1ec2f55' then dp.keep_watch_person_name else '' end,'') as  morningName, " +
            "  string_agg(case shift_id when  '0e0220e4af825d70d0932cc4504cd80d' then dp.keep_watch_person_name else '' end,'') as  noonName, " +
            "  string_agg(case shift_id when  '70b1dacd7bf4db32cd37c1322a290005' then dp.keep_watch_person_name else '' end,'') as  nightName " +
            " from dispatch_keep_watch  dw  " +
            " left join dispatch_keep_watch_person dp on dp.keep_watch_person_id = dw.lead_person_ids  " +
            " where dw.type= '2' and dp.del_flag = '0' and dw.del_flag ='0' and  to_char(\"keep_watch_date\",'yyyy-mm') =#{query} " +
            " <if test='mineCode != null and mineCode != \"\" '> "+
            " and dw.mine_code = #{mineCode} " +
            " </if>"+
            "  group by keep_watch_date " +
            "</script>")
    List<DispatchKeepWatchVO> leadShiftListExport(@Param("query") String beginDate,@Param("mineCode") String mineCode);



//    @Select("<script>" +
//            " select   to_char(keep_watch_date,'dd') as dutyId , " +
//            "  string_agg(case shift_id when  '0298c8a9d0639d715a0925f7b1ec2f55' then dp.keep_watch_person_name else null end,',') as  morningName, " +
//            "  string_agg(case shift_id when  '0e0220e4af825d70d0932cc4504cd80d' then dp.keep_watch_person_name else null end,',') as  noonName, " +
//            "  string_agg(case shift_id when  '70b1dacd7bf4db32cd37c1322a290005' then dp.keep_watch_person_name else null end,',') as  nightName " +
//            " from dispatch_keep_watch  dw  " +
//            " left join dispatch_keep_watch_person dp on dp.keep_watch_person_id = dw.lead_person_ids  " +
//            " where dw.type= '2' and dp.del_flag = '0' and dw.del_flag ='0' and  to_char(\"keep_watch_date\",'yyyy') =#{query} " +
//            " and post_type = #{flag} "+
//            "  group by keep_watch_date " +
//            "</script>")
    @Select("<script>" +
            "  select keep_watch_date, string_agg(chard,'') as dutyId," +
            " string_agg(morningName, '') as morningName,string_agg(morningName11,'') as morningStaff," +
            " string_agg(noonName, '') as noonName,string_agg(noonName1,'') as noonStaff ," +
            " string_agg(nightName, '') as nightName,string_agg(nightName1,'') as nightStaff" +
            " from (" +
            " select  keep_watch_date , to_char(keep_watch_date,'dd') as chard," +
            "              string_agg(case shift_id  when  '0298c8a9d0639d715a0925f7b1ec2f55'  then dp.keep_watch_person_name else null end,'' ) as  morningName, " +
            "              string_agg(case shift_id when  '0e0220e4af825d70d0932cc4504cd80d' then dp.keep_watch_person_name else '' end,'') as  noonName, " +
            "              string_agg(case shift_id when  '70b1dacd7bf4db32cd37c1322a290005' then dp.keep_watch_person_name else '' end,'') as  nightName ," +
            " null as morningName11," +
            " null as noonName1," +
            " null as nightName1" +
            "             from dispatch_keep_watch  dw  " +
            "            left join dispatch_keep_watch_person dp on dp.keep_watch_person_id = dw.lead_person_ids  " +
            "             where dw.type= '2' and dp.del_flag = '0' and dw.del_flag ='0' and  to_char(keep_watch_date,'yyyy') =#{query} and post_type = '2'" +
            " <if test='mineCode != null and mineCode != \"\" '> "+
            " and dw.mine_code = #{mineCode} " +
            " </if>"+
            "              group by keep_watch_date" +
            " union all " +
            " select  keep_watch_date , null as chard," +
            " null as morningName," +
            " null as noonName," +
            " null as nightName," +
            "              string_agg(case shift_id  when  '0298c8a9d0639d715a0925f7b1ec2f55'  then dp.keep_watch_person_name else null end,'' ) as  morningName11, " +
            "              string_agg(case shift_id when  '0e0220e4af825d70d0932cc4504cd80d' then dp.keep_watch_person_name else '' end,'') as  noonName1, " +
            "              string_agg(case shift_id when  '70b1dacd7bf4db32cd37c1322a290005' then dp.keep_watch_person_name else '' end,'') as  nightName1 " +
            "             from dispatch_keep_watch  dw  " +
            "            left join dispatch_keep_watch_person dp on dp.keep_watch_person_id = dw.lead_person_ids  " +
            "             where dw.type= '2' and dp.del_flag = '0' and dw.del_flag ='0' and  to_char(keep_watch_date,'yyyy') =#{query} and post_type = '1'" +
            " <if test='mineCode != null and mineCode != \"\" '> "+
            " and dw.mine_code = #{mineCode} " +
            " </if>"+
            "              group by keep_watch_date) a group by keep_watch_date " +
            "</script>")
    List<DispatchKeepWatchVO> leadShiftListExportGty(@Param("query") String beginDate,@Param("mineCode") String flag);


    @Select("<script>" +
            "  select keep_watch_date ,dw.type,dp.keep_watch_person_name as dutyPersonNames,dw.duty_person_ids,dw.keep_watch_id " +
            "  from dispatch_keep_watch dw  " +
            "  left join dispatch_keep_watch_person dp on dp.keep_watch_person_id = dw.duty_person_ids  " +
            "  where (type = '1' or type = '3' ) and dp.del_flag = '0' and dw.del_flag ='0' and  to_char(\"keep_watch_date\",'yyyy-mm') =#{query} " +
            " <if test='mineCode != null and mineCode != \"\" '> "+
            " and dp.mine_code = #{mineCode} " +
            " </if>"+
            "  order by keep_watch_date " +
            "</script>")
    List<DispatchKeepWatchVO> dutyShiftList(@Param("query") String beginDate,@Param("mineCode") String mineCode);



    @Select("<script>" +
            " select dp.keep_watch_person_name  as leadPersonNames , dp.keep_watch_person_id , " +
            " sum(case dw.shift_id when  '0298c8a9d0639d715a0925f7b1ec2f55' then 1 else 0 end) as morningNum , " +
            " sum(case dw.shift_id when   '0e0220e4af825d70d0932cc4504cd80d' then 1 else 0 end) as noonNum , " +
            " sum(case dw.shift_id when   '70b1dacd7bf4db32cd37c1322a290005' then 1 else 0 end) as nightNum  " +
            " from dispatch_keep_watch_person dp " +
            " left join dispatch_keep_watch dw on dp.keep_watch_person_id = dw.lead_person_ids  " +
            " where dw.type= '2' and dp.del_flag = '0' and dw.del_flag ='0' and  to_char(\"keep_watch_date\",'yyyy-mm') =#{query} " +
            " <if test='mineCode != null and mineCode != \"\" '> "+
            " and dw.mine_code = #{mineCode} " +
            " </if>"+
            " group by dp.keep_watch_person_name,dp.keep_watch_person_id " +
            "</script>")
    List<DispatchKeepWatchVO> leadShiftStatistics(@Param("query") String beginDate,@Param("mineCode")String mineCode);


    @Select("<script> " +
            "  select  dp.keep_watch_person_name as  dutyPersonNames, " +
            "  count(1) as dutyNum  " +
            "  from dispatch_keep_watch_person dp   " +
            "  left join dispatch_keep_watch dw on dp.keep_watch_person_id = dw.duty_person_ids    " +
            "   where dw.type= '1' and dp.del_flag = '0' and dw.del_flag ='0' and  to_char(\"keep_watch_date\",'yyyy-mm')  =#{query} " +
            "  <if test='mineCode != null and mineCode != \"\" '> "+
            "  and dw.mine_code = #{mineCode} " +
            "  </if>"+
            "   group by dp.keep_watch_person_name   " +
            "   order by dutyNum " +
            "</script>")
    List<DispatchKeepWatchVO> dutyShiftStatistics(@Param("query") String beginDate, @Param("mineCode") String mineCode);

    @Select("<script>" +
            " select count(1) from dispatch_keep_watch where (type = '1' or type = '2') and to_char(\"keep_watch_date\",'yyyy-mm') = #{query} " +
            "</script>")
    int selectCountByYearMonth(@Param("query") String beginDate);

    @Select("<script>" +
            "  select dw.duty_person_ids from dispatch_keep_watch dw  " +
            "  left join dispatch_keep_watch_person dp on dp.keep_watch_person_id = dw.duty_person_ids  where type='1'  " +
            "   and dp.del_flag = '0' and dw.del_flag ='0' and  to_char(dw.keep_watch_date,'yyyy-mm') =#{query}  " +
            " group by dw.duty_person_ids " +
            "</script>")
    List<DispatchKeepWatchVO> dutyList(@Param("query") String beginDate);


    @Select("<script>" +
            "  select dp.keep_watch_person_name as dutyPersonNames,dw.keep_watch_date " +
            "   from dispatch_keep_watch dw  " +
            "  left join dispatch_keep_watch_person dp on dp.keep_watch_person_id = dw.duty_person_ids    " +
            "  where type = '1' and dp.del_flag = '0' and dw.del_flag ='0' and  to_char(dw.keep_watch_date,'yyyy-mm') =#{query}  " +
            "  and dw.duty_person_ids =#{dutyPersonIds} " +
            "  group by dp.keep_watch_person_id,dw.keep_watch_date " +
            "</script>")
    List<DispatchKeepWatchVO> allDutyList(@Param("query") String beginDate,@Param("dutyPersonIds") String dutyPersonIds);


    @Update("<script>" +
            " update  dispatch_keep_watch set  del_flag = '1' " +
            " where keep_watch_date = to_date( #{query},'YYYY-MM-DD') and type = '2' and del_flag='0' and shift_id=#{shiftId} and lead_person_ids=#{leadId} " +
            "</script>")
    void deleteLeadByDate(@Param("query") String beginDate,@Param("shiftId") String shiftId,@Param("leadId") String leadId);

    @Update("<script>" +
            " update  dispatch_keep_watch set  del_flag = '1' " +
            " where keep_watch_date = to_date( #{query},'YYYY-MM-DD') and type = '2' and del_flag='0' and shift_id=#{shiftId} " +
            "</script>")
    void deleteLeadByDateAll(@Param("query") String beginDate,@Param("shiftId") String shiftId);


    @Update("<script>" +
            " update  dispatch_keep_watch set  del_flag = '1' " +
            " where keep_watch_date = to_date( #{query},'YYYY-MM-DD') and type = '2' and del_flag='0' and shift_id=#{shiftId} " +
            "</script>")
    void deleteByDate(@Param("query") String beginDate,@Param("shiftId") String shiftId);

    @Update("<script>" +
            " update  dispatch_keep_watch set  del_flag = '1' " +
            " where keep_watch_date = to_date( #{query},'YYYY-MM-DD') and type = '1' and del_flag='0' " +
            "</script>")
    void deleteDutyByDate(@Param("query") String beginDate);

    @Update("<script>" +
            " update  dispatch_keep_watch set  del_flag = '1' " +
            " where keep_watch_date = to_date( #{query},'YYYY-MM-DD') and type = '2' and del_flag='0' and shift_id=#{shiftId} " +
            "</script>")
    void deleteDutyByLead(@Param("query") String beginDate,@Param("shiftId") String shiftId);


    @Update("<script>" +
            " update  dispatch_keep_watch set  del_flag = '1' " +
            " where  to_char(keep_watch_date,'yyyy-mm') =#{query}  and del_flag='0' " +
            "</script>")
    void deleteByDate(@Param("query") String beginDate);


    @Select("<script> select count(1) from ( select keep_watch_date from dispatch_keep_watch where to_char(keep_watch_date,'yyyy-mm') =#{query} and del_flag = '0' group by keep_watch_date " +
            ") as dispatch_keep_watch </script>")
    int countMatic(@Param("query") String beginDate);


    @Update("<script>" +
            " update  dispatch_keep_watch set  del_flag = '1' " +
            " where keep_watch_date = to_date( #{query},'YYYY-MM-DD') and type = '2' and del_flag='0' and shift_id=#{shiftId} " +
            "</script>")
    void deleteGtyByDate(@Param("query") String beginDate,@Param("shiftId") String shiftId);

    @InterceptorIgnore(tenantLine = "true")
//    @SqlParser(filter=true)
    @Update("<script>" +
            " ${query} where mine_code = #{mineCode} " +
            "</script>")
    void daleteByDateGty(@Param("query") String beginDate,@Param("mineCode") String mineCode);
}
